package com.hdw.common.util;

import com.hdw.common.base.BaseException;
import com.hdw.common.db.DynamicDataSource;
import com.hdw.common.db.HikariDataSourceExt;
import com.hdw.common.vo.MetaDataVO;
import org.apache.commons.lang3.tuple.ImmutableTriple;
import org.apache.commons.lang3.tuple.Triple;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.Date;
import java.util.*;

public class DBUtil {
    public static final String DB_DW = "DW";
    public static final String META_DB = "0";
    public static final String DB_TYPE_ORACLE = "oracle";
    public static final String DB_TYPE_SQLSERVER = "sqlserver";
    public static final String DB_TYPE_MYSQL = "mysql";


    public static void closeDataSource(Object dataSource) {
        if (dataSource == null) return;
        if (dataSource.getClass().equals(HikariDataSourceExt.class)) {
            ((HikariDataSourceExt) dataSource).close();
        }
    }


    public static Set<Object> getDbName() {
        Set<Object> objects = DynamicDataSource.getDataSourceMap().keySet();
        return objects;
    }

    public static DataSource getDataSource(String idDb) {
        DynamicDataSource bean = SpringContextUtil.getBean(DynamicDataSource.class);
        DynamicDataSource.setDataSource(idDb);
        return bean;
    }

    public static Connection getOtherConnection(String idConnIn) throws SQLException {
        DynamicDataSource bean = SpringContextUtil.getBean(DynamicDataSource.class);

        String dataSource = DynamicDataSource.getDataSource();

        DynamicDataSource.setDataSource(idConnIn);

        Connection connection = bean.getConnection();

        DynamicDataSource.setDataSource(dataSource);

        return connection;
    }

    public static void closeDBResources(ResultSet rs, Statement stmt,
                                        Connection conn) {
        if (null != rs) {
            try {
                rs.close();
            } catch (SQLException unused) {
            }
        }

        if (null != stmt) {
            try {
                stmt.close();
            } catch (SQLException unused) {
            }
        }

        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException unused) {
            }
        }
    }

    public static ResultSet query(Connection conn, String sql, int fetchSize, int queryTimeout)
            throws SQLException {
        conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(fetchSize);
        stmt.setQueryTimeout(queryTimeout);
        return query(stmt, sql);
    }


    public static ResultSet testQuery(Connection conn, String sql)
            throws SQLException {
        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(10);
        return query(stmt, sql);
    }

    public static ResultSet query(Statement stmt, String sql)
            throws SQLException {
        return stmt.executeQuery(sql);
    }


    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaDataTo(DataSource ds, String tableName) throws BaseException {
        Connection connection = null;
        try {
            connection = ds.getConnection();
            Triple<List<String>, List<Integer>, List<String>> columnMetaData = getColumnMetaData(connection, tableName, " * ");
            return columnMetaData;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.closeDBResources(null, null, connection);
        }
        return null;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataToMap(DataSource ds, String tableName) throws BaseException {
        Connection connection = null;
        try {
            connection = ds.getConnection();
            Map<String, Triple<String, Integer, String>> columnMetaDataToMap = getColumnMetaDataToMap(connection, tableName, " * ");
            return columnMetaDataToMap;

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtil.closeDBResources(null, null, connection);
        }
        return null;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, Triple<String, Integer, String>> getColumnMetaDataToMap(
            Connection conn, String tableName, String column) throws BaseException {
        Triple<List<String>, List<Integer>, List<String>> columnMetaData = DBUtil.getColumnMetaData(conn, tableName, column);

        Map<String, Triple<String, Integer, String>> map = new HashMap<>();
        List<String> left = columnMetaData.getLeft();
        List<Integer> middle = columnMetaData.getMiddle();
        List<String> right = columnMetaData.getRight();
        for (int i = 0; i < left.size(); i++) {
            Triple<String, Integer, String> t = new ImmutableTriple(left.get(i), middle.get(i), right.get(i));
            map.put(left.get(i), t);
        }
        return map;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(
            Connection conn, String tableName, String column) throws BaseException {
        Statement statement = null;
        ResultSet rs = null;
        try {
            statement = conn.createStatement();

            String queryColumnSql = "select " + column + " from " + tableName
                    + " where 1=2";


            rs = statement.executeQuery(queryColumnSql);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaData(rsMetaData);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new BaseException(String.format("获取表:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", tableName), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, null);
        }
    }

    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(ResultSetMetaData rsMetaData) throws BaseException {

        Triple<List<String>, List<Integer>, List<String>> columnMetaData = new ImmutableTriple<>(
                new ArrayList<>(), new ArrayList<>(),
                new ArrayList<>());
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                columnMetaData.getLeft().add(rsMetaData.getColumnLabel(i + 1));
                columnMetaData.getMiddle().add(rsMetaData.getColumnType(i + 1));
                columnMetaData.getRight().add(rsMetaData.getColumnTypeName(i + 1));
            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."), e);
        }
    }

    public static Boolean tableIsExist(String tableName, String dataSourceID) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));

        Map<String, MetaDataVO> tableColumnMetaData = null;
        try {
            tableColumnMetaData = DBUtil.getTableColumnMetaDataToMetaDataVO(dataSource, tableName);
        } catch (Exception e) {
        }

        return tableColumnMetaData != null;
    }

    /**
     * @return Left:ColumnName Middle:ColumnType Right:ColumnTypeName
     */
    public static Map<String, MetaDataVO> getTableColumnMetaDataToMetaDataVO(HikariDataSourceExt ds, String tableName) throws BaseException {
        String queryColumnSql = "select * from " + tableName + " where 1=2";
        return getColumnMetaDataToMetaDataVO(ds, queryColumnSql);

    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param dataSource
     * @param sql
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(
            HikariDataSourceExt dataSource, String sql) throws BaseException {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        String qSQL = null;

        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            qSQL = EtlUtil.buildSql(sql, new Date(), new Date(), dataSource.getEuTp());
            rs = statement.executeQuery(qSQL);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaDataToMetaDataVO(rsMetaData);
        } catch (SQLException e) {
            //e.printStackTrace();
            throw new BaseException(String.format("获取查询:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", qSQL), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, connection);
        }
    }

    /**
     * Map<ColumnName, MetaDataVO>
     *
     * @param rsMetaData
     * @return
     * @throws BaseException
     */
    public static Map<String, MetaDataVO> getColumnMetaDataToMetaDataVO(ResultSetMetaData rsMetaData) throws BaseException {

        Map<String, MetaDataVO> columnMetaData = new LinkedHashMap<>();
        try {
            for (int i = 0, len = rsMetaData.getColumnCount(); i < len; i++) {
                MetaDataVO metaDataVO = new MetaDataVO();
                metaDataVO.setColumnName(rsMetaData.getColumnLabel(i + 1).toUpperCase());
                metaDataVO.setColumnType(rsMetaData.getColumnType(i + 1));
                metaDataVO.setColumnTypeName(rsMetaData.getColumnTypeName(i + 1));
                metaDataVO.setColumnPrecision(rsMetaData.getPrecision(i + 1));
                metaDataVO.setColumnScale(rsMetaData.getScale(i + 1));
                columnMetaData.put(metaDataVO.getColumnName(), metaDataVO);

            }
            return columnMetaData;

        } catch (SQLException e) {
            throw new BaseException(String.format("获取表字段的元信息时失败. 请联系 DBA 核查该库、表信息."), e);
        }
    }

    public static Triple<List<String>, List<Integer>, List<String>> getColumnMetaData(
            HikariDataSourceExt dataSource, String sql) throws BaseException {
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        String qSQL = null;

        try {
            connection = dataSource.getConnection();
            statement = connection.createStatement();
            qSQL = EtlUtil.buildSql(sql, new Date(), new Date(), dataSource.getEuTp());
            rs = statement.executeQuery(qSQL);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            return getColumnMetaData(rsMetaData);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new BaseException(String.format("获取查询:%s 的字段的元信息时失败. 请联系 DBA 核查该库、表信息.", qSQL), e);
        } finally {
            DBUtil.closeDBResources(rs, statement, connection);
        }
    }


    /**
     * ColumnType  ColumnTypeName
     *
     * @return
     * @throws IllegalAccessException
     */
    public static Map<Integer, String> getJdbcColumnTypeMap() throws IllegalAccessException {
        Map<Integer, String> jbcColumnTypeMap = new HashMap<>();

        Field[] types = Types.class.getDeclaredFields();
        for (Field typeField : types) {
            jbcColumnTypeMap.put(typeField.getInt(null), typeField.getName());
        }

        return jbcColumnTypeMap;
    }

    public static String getColumnType(Integer numLth, Integer numPrec) {
        String cType = "";
        if (numLth != null && numLth > 0) {
            if (numLth != null && numLth > 0) {
                cType += "(" + numLth + "," + numPrec + ")";
            } else {
                cType += "(" + numLth + ")";
            }

        }
        return cType;
    }

    public static void dropTable(String tbTar, String dataSourceID) throws BaseException {
        String dropSQL = String.format("DROP TABLE %s ;", tbTar);
        execOneSQL(dropSQL, dataSourceID);
    }

    public static void renameTable(String oldTb, String newTb,String dataSourceID) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        String euDbTp = dataSource == null ? null : dataSource.getEuTp();
        String renameSql = "";
        switch (euDbTp) {
            case DBUtil.DB_TYPE_MYSQL:
                renameSql = "RENAME TABLE " + oldTb + " TO " + newTb + ";";
                break;
            case DBUtil.DB_TYPE_SQLSERVER:
                renameSql = "EXEC sp_rename " + oldTb + "," + newTb + ";";
                break;
            case DBUtil.DB_TYPE_ORACLE:
                renameSql = "RENAME " + oldTb + " TO " + newTb + ";";
                break;
        }
        execOneSQL(renameSql, dataSourceID);
    }


    public static void execOneSQL(String sql, String dataSourceID) throws BaseException {
        HikariDataSourceExt dataSource = DynamicDataSource.getHikariDataSourceById(dataSourceID);
        if (dataSource == null) throw new BaseException(String.format("目标获取数据库[%s]失败", dataSourceID));
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);

            Statement statement = connection.createStatement();

            statement.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BaseException("执行SQL错误! 错误为:" + e.getMessage());
        } finally {
            DBUtil.closeDBResources(null, preparedStatement, connection);
        }
    }
}
